package com.shuyun.ccms.encrypt.common;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.shuyun.ccms.encrypt.util.ConnectionUtil;
import com.shuyun.crypt.CryptTools;

public class DataLoad {
	private static Logger logger = LoggerFactory.getLogger(ConnectionUtil.class);

	public static void loadTaobaoCustomerInfo(Connection conn,String filePath){
		logger.info("load 淘宝客戶基本信息開始");
		long start = System.currentTimeMillis();
		Statement   statement = null;
		try {
/**
 * 原始数据栏位:             customerno, full_name , mobile , phone , fullname_modified , mobile_modified , fullname_most , mobile_most , phone_most
 * 加密后数据栏位:customerno,customerno_encrypt,full_name_encrypt, mobile_encrypt,phone_encrypt,fullname_modified_encrypt,mobile_modified_encrypt,fullname_most_encrypt,mobile_most_encrypt,phone_most_encrypt
 */
            StringBuffer sqlLoadStr = new StringBuffer("LOAD DATA LOCAL INFILE '").append(filePath).append("' ");
            sqlLoadStr.append("IGNORE INTO TABLE ").append(TargetTable.TAOBAO_CUSTOMER_INFO.getValue()).append(" CHARACTER SET UTF8 ");
            sqlLoadStr.append(" fields terminated by ',' ");
            //sqlLoadStr.append("LINES TERMINATED BY '\\r\\n' ");
            sqlLoadStr.append("(customerno,customerno_encrypt,full_name_encrypt, mobile_encrypt,phone_encrypt,fullname_modified_encrypt,mobile_modified_encrypt,fullname_most_encrypt,mobile_most_encrypt,phone_most_encrypt)");

            statement = conn.createStatement();
            statement.execute(sqlLoadStr.toString());
            long end = System.currentTimeMillis();
            conn.commit();
            logger.info("load 淘宝客戶基本信息結束, 耗时: "+ (end-start)/1000+ " s");
        } catch (Exception ex) {
        	try {
				conn.rollback();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
            logger.error("load 淘宝客戶基本信息异常：", ex);
            throw new RuntimeException("load 淘宝客戶基本信息异常："+ ex);
        } finally {
            try {
                if (statement != null) {
                    statement.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
	}

	/**
	 * 原始数据栏位:   pin, full_name , mobile , telephone
	 * 加密后数据栏位: customerno, customerno_encrypt,full_name_encrypt, mobile_encrypt,telephone_encrypt
	 */
	public static void loadJdCustomerInfo(Connection conn,String filePath){
		logger.info("load 京东客戶基本信息開始");
		long start = System.currentTimeMillis();
		Statement statement = null;
		try {
            StringBuffer sqlLoadStr = new StringBuffer("LOAD DATA LOCAL INFILE '").append(filePath).append("' ");
            sqlLoadStr.append("IGNORE INTO TABLE ").append(TargetTable.JD_CUSTOMER_INFO.getValue()).append(" CHARACTER SET UTF8 ");
            sqlLoadStr.append(" fields terminated by ',' ");
          //  sqlLoadStr.append("LINES TERMINATED BY '\\r\\n' ");
            sqlLoadStr.append("(customerno, customerno_encrypt,full_name_encrypt, mobile_encrypt,telephone_encrypt)");

            statement = conn.createStatement();
            statement.execute(sqlLoadStr.toString());
            long end = System.currentTimeMillis();
            conn.commit();
            logger.info("load 京东客戶基本信息結束, 耗时: "+ (end-start)/1000+ " s");
        } catch (Exception ex) {
        	try {
				conn.rollback();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
            logger.error("load 京东客戶基本信息异常：", ex);
            throw new RuntimeException("load 京东客戶基本信息异常："+ ex);
        } finally {
            try {
                if (statement != null) {
                    statement.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
       }
}

	/**
	 * 原始数据栏位:   customerno,full_name,mobile,phone,fullname_most,mobile_most,phone_most
	 * 加密后数据栏位: customerno, customerno_encrypt,full_name_encrypt, mobile_encrypt,phone_encrypt,fullname_most_encrypt,mobile_most_encrypt,phone_most_encrypt
	 */
	public static void loadYhdCustomerInfo(Connection conn,String filePath){
		logger.info("load 一号店客戶基本信息開始");
		long start = System.currentTimeMillis();
		Statement statement = null;
		try {
            StringBuffer sqlLoadStr = new StringBuffer("LOAD DATA LOCAL INFILE '").append(filePath).append("' ");
            sqlLoadStr.append("IGNORE INTO TABLE ").append(TargetTable.YHD_CUSTOMER_INFO.getValue()).append(" CHARACTER SET UTF8 ");
            sqlLoadStr.append(" fields terminated by ',' ");
          //  sqlLoadStr.append("LINES TERMINATED BY '\\r\\n' ");
            sqlLoadStr.append("(customerno, customerno_encrypt,full_name_encrypt, mobile_encrypt,phone_encrypt,fullname_most_encrypt,mobile_most_encrypt,phone_most_encrypt)");

            statement = conn.createStatement();
            statement.execute(sqlLoadStr.toString());
            long end = System.currentTimeMillis();
            conn.commit();
            logger.info("load 京东客戶基本信息結束, 耗时: "+ (end-start)/1000+ " s");
        } catch (Exception ex) {
        	try {
				conn.rollback();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
            logger.error("load 一号店客戶基本信息异常：", ex);
            throw new RuntimeException("load 一号店客戶基本信息异常："+ ex);
        } finally {
            try {
                if (statement != null) {
                    statement.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
	}

	/**
	 * 原始数据栏位:   customerno, full_name , mobile , phone, fullname_most , mobile_most , phone_most
	 * 加密后数据栏位: customerno, customerno_encrypt,full_name_encrypt, mobile_encrypt,phone_encrypt,fullname_most_encrypt,mobile_most_encrypt,phone_most_encrypt
	 */
	public static void loadDdCustomerInfo(Connection conn,String filePath){
		logger.info("load 当当客戶基本信息開始");
		long start = System.currentTimeMillis();
		Statement statement = null;
		try {
            StringBuffer sqlLoadStr = new StringBuffer("LOAD DATA LOCAL INFILE '").append(filePath).append("' ");
            sqlLoadStr.append("IGNORE INTO TABLE ").append(TargetTable.DD_CUSTOMER_INFO.getValue()).append(" CHARACTER SET UTF8 ");
            sqlLoadStr.append(" fields terminated by ',' ");
           // sqlLoadStr.append("LINES TERMINATED BY '\\r\\n' ");
            sqlLoadStr.append("(customerno, customerno_encrypt,full_name_encrypt, mobile_encrypt,phone_encrypt,fullname_most_encrypt,mobile_most_encrypt,phone_most_encrypt)");

            statement = conn.createStatement();
            statement.execute(sqlLoadStr.toString());
            long end = System.currentTimeMillis();
            conn.commit();
            logger.info("load 当当客戶基本信息結束, 耗时: "+ (end-start)/1000+ " s");
        } catch (Exception ex) {
        	try {
				conn.rollback();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
            logger.error("load 当当客戶基本信息异常：", ex);
            throw new RuntimeException("load 当当客戶基本信息异常："+ ex);
        } finally {
            try {
                if (statement != null) {
                    statement.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
	}

	/**
	 * 原始数据栏位:   customerno, customer_name ,customer_name_modify, customer_phone , customer_phone_modify, invoice_recipient_name , invoice_recipient_phone , invoice_recipient_hand_phone
	 * 加密后数据栏位: customerno, customerno_encrypt,customer_name_encrypt, customer_name_modify_encrypt, customer_phone_encrypt,customer_phone_modify_encrypt,invoice_recipient_name_encrypt,invoice_recipient_phone_encrypt,invoice_recipient_hand_phone_encrypt
	 */
	public static void loadSuningCustomerInfo(Connection conn,String filePath){
		logger.info("load 苏宁客戶基本信息開始");
		long start = System.currentTimeMillis();
		Statement statement = null;
		try {
            StringBuffer sqlLoadStr = new StringBuffer("LOAD DATA LOCAL INFILE '").append(filePath).append("' ");
            sqlLoadStr.append("IGNORE INTO TABLE ").append(TargetTable.SUNING_CUSTOMER_INFO.getValue()).append(" CHARACTER SET UTF8 ");
            sqlLoadStr.append(" fields terminated by ',' ");
           // sqlLoadStr.append("LINES TERMINATED BY '\\r\\n' ");
            sqlLoadStr.append("(customerno, customerno_encrypt,customer_name_encrypt, customer_name_modify_encrypt, customer_phone_encrypt,customer_phone_modify_encrypt,invoice_recipient_name_encrypt,invoice_recipient_phone_encrypt,invoice_recipient_hand_phone_encrypt)");

            statement = conn.createStatement();
            statement.execute(sqlLoadStr.toString());
            conn.commit();
            long end = System.currentTimeMillis();
            logger.info("load 苏宁客戶基本信息結束, 耗时: "+ (end-start)/1000+ " s");
         } catch (Exception ex) {
        	try {
 				conn.rollback();
 			} catch (SQLException e) {
 				// TODO Auto-generated catch block
 				e.printStackTrace();
 			}
            logger.error("load 苏宁客戶基本信息异常：", ex);
            throw new RuntimeException("load 苏宁客戶基本信息异常："+ ex);
         } finally {
            try {
                if (statement != null) {
                    statement.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
	}

	public static void loadPhoneInfo(Connection conn,String filePath,String tableName,String platForm){
		logger.info("load 平台:" + platForm + " 客戶手机号码開始");
		long start = System.currentTimeMillis();
		Statement statement = null;
		try {
            StringBuffer sqlLoadStr = new StringBuffer("LOAD DATA LOCAL INFILE '").append(filePath).append("' ");
            sqlLoadStr.append("IGNORE INTO TABLE ").append(tableName).append(" CHARACTER SET UTF8 ");
            sqlLoadStr.append(" fields terminated by ',' ");
          //  sqlLoadStr.append("LINES TERMINATED BY '\\r\\n' ");
            sqlLoadStr.append("(phone, phone_encrypt)");

            statement = conn.createStatement();
            statement.execute(sqlLoadStr.toString());
            conn.commit();
            long end = System.currentTimeMillis();
            logger.info("load 平台:" + platForm + " 客戶手机号码結束, 耗时: "+ (end-start)/1000+ " s");
         } catch (Exception ex) {
        	try {
 				conn.rollback();
 			} catch (SQLException e) {
 				// TODO Auto-generated catch block
 				e.printStackTrace();
 			}
            logger.error("load 平台:" + platForm + " 客戶手机号码异常：", ex);
            throw new RuntimeException("load 平台:" + platForm + "  客戶手机号码异常："+ ex);
         } finally {
            try {
                if (statement != null) {
                    statement.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
	}

	public static void loadOrderInfo(Connection conn,String filePath,String tableName,String platForm){
		logger.info("load 平台:" + platForm + " 订单信息开始");
		long start = System.currentTimeMillis();
		Statement statement = null;
		try {
            StringBuffer sqlLoadStr = new StringBuffer("LOAD DATA LOCAL INFILE '").append(filePath).append("' ");
            sqlLoadStr.append("IGNORE INTO TABLE ").append(tableName).append(" CHARACTER SET UTF8 ");
            sqlLoadStr.append(" fields terminated by ',' ");
          //  sqlLoadStr.append("LINES TERMINATED BY '\\r\\n' ");
            sqlLoadStr.append("(order_id, customerno_encrypt,receiver_name_encrypt,receiver_mobile_encrypt)");

            statement = conn.createStatement();
            statement.execute(sqlLoadStr.toString());
            long end = System.currentTimeMillis();
            conn.commit();
            logger.info("load 平台:" + platForm + " 订单信息結束, 耗时: "+ (end-start)/1000+ " s");
         } catch (Exception ex) {
        	try {
 				conn.rollback();
 			} catch (SQLException e) {
 				// TODO Auto-generated catch block
 				e.printStackTrace();
 			}
            logger.error("load 平台:" + platForm + " 订单信息异常：", ex);
            throw new RuntimeException("load 平台:" + platForm + "  订单信息异常："+ ex);
         } finally {
            try {
                if (statement != null) {
                    statement.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
	}

	public static void saveErrorInfo(Connection conn,String customerNo,String line,String platForm,String orderId) throws Exception {
		PreparedStatement ps = null;
		StringBuffer sql = new StringBuffer();
		try {
			sql.append("insert into tb_customer_error_info(customerno, customer_info,plat_form,order_id ) values (?, ?, ?, ?) ");
			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, customerNo);
			ps.setString(2, line);
			ps.setString(3, platForm);
			ps.setString(4, orderId);
			conn.setAutoCommit(false);
			ps.executeUpdate();
			conn.commit();
		} catch (Exception e) {
			conn.rollback();
			e.printStackTrace();
			logger.error("记录tb_customer_error_info表失败。报错. " + e.getMessage());
			throw e;
		}finally{
			ConnectionUtil.newInstance().closePreparedStatement(ps);
		}
	}

	public static void updateDashCanct(Connection conn) throws Exception{
		//logger.info("加密ccms 系统联系人开始");
		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuffer sql = new StringBuffer();

		StringBuffer updSql = new StringBuffer();
		String encryptFullName = null;
		String encryptMobile = null;
		Integer id = null;
		String fullName = null;
		String mobile = null;
		try{
			sql.append(" select id, full_name, mobile from dashboard_contact ");
			ps = conn.prepareStatement(sql.toString());
			rs = ps.executeQuery();
			while(rs.next()){
				id = rs.getInt(1);
				fullName = rs.getString(2);
				mobile = rs.getString(3);
			}
			ConnectionUtil.newInstance().close(ps, rs);
			if(fullName != null){
				encryptFullName = CryptTools.encrypt(fullName, CryptTools.TYPE_RECEIVER_NAME);
			}
			if(mobile != null){
				encryptMobile = CryptTools.encrypt(mobile, CryptTools.TYPE_PHONE);
			}
			ConnectionUtil.newInstance().close(ps, rs);

			updSql.append(" update dashboard_contact  set full_name = ?, mobile = ? where id = ?");
			ps = conn.prepareStatement(updSql.toString());
			ps.setString(1, encryptFullName);
			ps.setString(2, encryptMobile);
			ps.setInt(3, id);
			ps.execute();
			// logger.info("加密ccms 系统联系人结束");
		}catch(Exception e){
			logger.error("加密ccms 系统联系人失败." + e.getMessage());
			throw e;
		}finally{
			ConnectionUtil.newInstance().close(ps, rs);
		}
	}
}
